package com.school.dao;

import com.school.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 学生DAO
 */
@Repository
public class StudentDao {
    
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    /**
     * 插入学生
     */
    public Long insert(Student student) {
        String sql = "INSERT INTO tb_student (student_id, name, gender, age, class_name, teacher_name, major, " +
                "building, dormitory, phone, status, remark,bedNumber) " +
                "VALUES (:studentId, :name, :gender, :age, :className, :teacherName, :major, " +
                ":building, :dormitory, :phone, :status, :remark,:bedNumber)";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("studentId", student.getStudentId())
                .addValue("name", student.getName())
                .addValue("gender", student.getGender())
                .addValue("age", student.getAge())
                .addValue("className", student.getClassName())
                .addValue("teacherName", student.getTeacherName())
                .addValue("major", student.getMajor())
                .addValue("building", student.getBuilding())
                .addValue("dormitory", student.getDormitory())
                .addValue("phone", student.getPhone())
                .addValue("status", student.getStatus())
                .addValue("bedNumber", student.getBedNumber())
                .addValue("remark", student.getRemark());
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, params, keyHolder);
        return keyHolder.getKey().longValue();
    }
    
    /**
     * 更新学生
     */
    public int update(Student student) {
        String sql = "UPDATE tb_student SET name = :name, gender = :gender, age = :age, " +
                "class_name = :className, teacher_name = :teacherName, major = :major, " +
                "building = :building, dormitory = :dormitory, phone = :phone, " +
                "status = :status, bedNumber = :bedNumber, remark = :remark WHERE id = :id";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("id", student.getId())
                .addValue("name", student.getName())
                .addValue("gender", student.getGender())
                .addValue("age", student.getAge())
                .addValue("className", student.getClassName())
                .addValue("teacherName", student.getTeacherName())
                .addValue("major", student.getMajor())
                .addValue("building", student.getBuilding())
                .addValue("dormitory", student.getDormitory())
                .addValue("phone", student.getPhone())
                .addValue("status", student.getStatus())
                .addValue("bedNumber", student.getBedNumber())
                .addValue("remark", student.getRemark());
        
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 删除学生
     */
    public int deleteById(Long id) {
        String sql = "DELETE FROM tb_student WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 批量删除学生
     */
    public int batchDelete(List<Long> ids) {
        String sql = "DELETE FROM tb_student WHERE id IN (:ids)";
        MapSqlParameterSource params = new MapSqlParameterSource("ids", ids);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 根据ID查询学生
     */
    public Student findById(Long id) {
        String sql = "SELECT * FROM tb_student WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        List<Student> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Student s = new Student();
                    s.setId(rs.getLong("id"));
                    s.setStudentId(rs.getString("student_id"));
                    s.setName(rs.getString("name"));
                    s.setGender(rs.getString("gender"));
                    s.setAge(rs.getInt("age"));
                    s.setClassName(rs.getString("class_name"));
                    s.setTeacherName(rs.getString("teacher_name"));
                    s.setMajor(rs.getString("major"));
                    s.setBuilding(rs.getString("building"));
                    s.setDormitory(rs.getString("dormitory"));
                    s.setPhone(rs.getString("phone"));
                    s.setStatus(rs.getString("status"));
                    s.setRemark(rs.getString("remark"));
                    s.setBedNumber(rs.getString("bedNumber"));
                    s.setCreateTime(rs.getTimestamp("create_time"));
                    s.setUpdateTime(rs.getTimestamp("update_time"));
                    return s;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 根据学号查询学生
     */
    public Student findByStudentId(String studentId) {
        String sql = "SELECT * FROM tb_student WHERE student_id = :studentId";
        MapSqlParameterSource params = new MapSqlParameterSource("studentId", studentId);
        List<Student> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Student s = new Student();
                    s.setId(rs.getLong("id"));
                    s.setStudentId(rs.getString("student_id"));
                    s.setName(rs.getString("name"));
                    s.setGender(rs.getString("gender"));
                    s.setAge(rs.getInt("age"));
                    s.setClassName(rs.getString("class_name"));
                    s.setTeacherName(rs.getString("teacher_name"));
                    s.setMajor(rs.getString("major"));
                    s.setBuilding(rs.getString("building"));
                    s.setDormitory(rs.getString("dormitory"));
                    s.setPhone(rs.getString("phone"));
                    s.setStatus(rs.getString("status"));
                    s.setBedNumber(rs.getString("bedNumber"));
                    s.setRemark(rs.getString("remark"));
                    s.setCreateTime(rs.getTimestamp("create_time"));
                    s.setUpdateTime(rs.getTimestamp("update_time"));
                    return s;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 分页查询学生列表
     */
    public List<Student> findByPage(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT * FROM tb_student WHERE 1=1");
        
        if (params.get("studentId") != null && !"".equals(params.get("studentId"))) {
            sql.append(" AND student_id LIKE CONCAT('%', :studentId, '%')");
        }
        if (params.get("name") != null && !"".equals(params.get("name"))) {
            sql.append(" AND name LIKE CONCAT('%', :name, '%')");
        }
        if (params.get("className") != null && !"".equals(params.get("className"))) {
            sql.append(" AND class_name = :className");
        }
        if (params.get("building") != null && !"".equals(params.get("building"))) {
            sql.append(" AND building = :building");
        }
        if (params.get("status") != null && !"".equals(params.get("status"))) {
            sql.append(" AND status = :status");
        }
        
        sql.append(" ORDER BY create_time DESC LIMIT :limit OFFSET :offset");
        
        return namedParameterJdbcTemplate.query(sql.toString(), params, 
                (rs, rowNum) -> {
                    Student s = new Student();
                    s.setId(rs.getLong("id"));
                    s.setStudentId(rs.getString("student_id"));
                    s.setName(rs.getString("name"));
                    s.setGender(rs.getString("gender"));
                    s.setAge(rs.getInt("age"));
                    s.setClassName(rs.getString("class_name"));
                    s.setTeacherName(rs.getString("teacher_name"));
                    s.setMajor(rs.getString("major"));
                    s.setBuilding(rs.getString("building"));
                    s.setDormitory(rs.getString("dormitory"));
                    s.setPhone(rs.getString("phone"));
                    s.setStatus(rs.getString("status"));
                    s.setBedNumber(rs.getString("bedNumber"));
                    s.setRemark(rs.getString("remark"));
                    s.setCreateTime(rs.getTimestamp("create_time"));
                    s.setUpdateTime(rs.getTimestamp("update_time"));
                    return s;
                });
    }
    
    /**
     * 统计总数
     */
    public long count(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM tb_student WHERE 1=1");
        
        if (params.get("studentId") != null && !"".equals(params.get("studentId"))) {
            sql.append(" AND student_id LIKE CONCAT('%', :studentId, '%')");
        }
        if (params.get("name") != null && !"".equals(params.get("name"))) {
            sql.append(" AND name LIKE CONCAT('%', :name, '%')");
        }
        if (params.get("className") != null && !"".equals(params.get("className"))) {
            sql.append(" AND class_name = :className");
        }
        if (params.get("building") != null && !"".equals(params.get("building"))) {
            sql.append(" AND building = :building");
        }
        if (params.get("status") != null && !"".equals(params.get("status"))) {
            sql.append(" AND status = :status");
        }
        
        Long count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Long.class);
        return count != null ? count : 0L;
    }
    
    /**
     * 批量插入
     */
    public int batchInsert(List<Student> students) {
        String sql = "INSERT INTO tb_student (student_id, name, gender, age, class_name, teacher_name, major, " +
                "building, dormitory, phone, status, remark,bedNumber) " +
                "VALUES (:studentId, :name, :gender, :age, :className, :teacherName, :major, " +
                ":building, :dormitory, :phone, :status, :remark,:bedNumber)";
        
        MapSqlParameterSource[] batchParams = new MapSqlParameterSource[students.size()];
        for (int i = 0; i < students.size(); i++) {
            Student student = students.get(i);
            batchParams[i] = new MapSqlParameterSource()
                    .addValue("studentId", student.getStudentId())
                    .addValue("name", student.getName())
                    .addValue("gender", student.getGender())
                    .addValue("age", student.getAge())
                    .addValue("className", student.getClassName())
                    .addValue("teacherName", student.getTeacherName())
                    .addValue("major", student.getMajor())
                    .addValue("building", student.getBuilding())
                    .addValue("dormitory", student.getDormitory())
                    .addValue("phone", student.getPhone())
                    .addValue("bedNumber", student.getBedNumber())
                    .addValue("status", student.getStatus())
                    .addValue("remark", student.getRemark());
        }
        
        int[] results = namedParameterJdbcTemplate.batchUpdate(sql, batchParams);
        return results.length;
    }
    
    /**
     * 查询未分配宿舍的学生
     */
    public List<Student> findUnallocatedStudents() {
        String sql = "SELECT * FROM tb_student WHERE dormitory IS NULL OR dormitory = '' ORDER BY class_name, gender, name";
        return namedParameterJdbcTemplate.query(sql, new HashMap<>(), 
                (rs, rowNum) -> {
                    Student s = new Student();
                    s.setId(rs.getLong("id"));
                    s.setStudentId(rs.getString("student_id"));
                    s.setName(rs.getString("name"));
                    s.setGender(rs.getString("gender"));
                    s.setAge(rs.getInt("age"));
                    s.setClassName(rs.getString("class_name"));
                    s.setTeacherName(rs.getString("teacher_name"));
                    s.setMajor(rs.getString("major"));
                    s.setBuilding(rs.getString("building"));
                    s.setDormitory(rs.getString("dormitory"));
                    s.setPhone(rs.getString("phone"));
                    s.setStatus(rs.getString("status"));
                    s.setBedNumber(rs.getString("bedNumber"));
                    s.setRemark(rs.getString("remark"));
                    s.setCreateTime(rs.getTimestamp("create_time"));
                    s.setUpdateTime(rs.getTimestamp("update_time"));
                    return s;
                });
    }
    
    /**
     * 根据宿舍查询学生
     */
    public List<Student> findByDormitory(String building, String roomNumber) {
        String sql = "SELECT * FROM tb_student WHERE building = :building AND dormitory = :roomNumber";
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("building", building)
                .addValue("roomNumber", roomNumber);
        
        return namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Student s = new Student();
                    s.setId(rs.getLong("id"));
                    s.setStudentId(rs.getString("student_id"));
                    s.setName(rs.getString("name"));
                    s.setGender(rs.getString("gender"));
                    s.setAge(rs.getInt("age"));
                    s.setClassName(rs.getString("class_name"));
                    s.setTeacherName(rs.getString("teacher_name"));
                    s.setMajor(rs.getString("major"));
                    s.setBuilding(rs.getString("building"));
                    s.setDormitory(rs.getString("dormitory"));
                    s.setPhone(rs.getString("phone"));
                    s.setStatus(rs.getString("status"));
                    s.setBedNumber(rs.getString("bedNumber"));
                    s.setRemark(rs.getString("remark"));
                    s.setCreateTime(rs.getTimestamp("create_time"));
                    s.setUpdateTime(rs.getTimestamp("update_time"));
                    return s;
                });
    }
}


